package jsyf.oa.dao.system;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.com.dao.BaseJdbcDao;
import jsyf.oa.entity.system.RegionEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class RegionDao extends BaseDao {
    private static Integer token = 1;

    public RegionDao() {
        this.iBaseEntity = new RegionEntity();
    }

    public RegionEntity getEntityById(String id) {
        return (RegionEntity) this.getIBaseEntityById(id);
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, RegionEntity.class);
    }

    public Boolean sort(String regionIds) {
        List<String> list = BaseJdbcDao.stringToList(regionIds);
        for (int i = 0; i < list.size(); i++) {
            JSONObject obj = new JSONObject();
            obj.put("ID", list.get(i));
            obj.put("SORT_NO", i);

            JSONObject objId = new JSONObject();
            objId.put("ID", list.get(i));
            super.update(iBaseEntity.getTableName(), obj, objId);
        }
        return true;
    }


    public List<JSONObject> getById(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT REGION_ID AS regionId FROM t_sys_user_2_region WHERE ");
        sb.append(" USER_ID =" + "'" + userId + "'");

        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getBigRegion(String condition) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t_sys_region.NAME AS name,t_sys_region.ID as id,t_sys_region.COMPANY_ID as companyId,t_sys_user_achievements.BIG_MANAGER_TARGER_NUM AS bigManagerTarget,t_sys_user_achievements.MANAGER_TARGET_NUM AS managerTarget " +
                "FROM t_sys_region left join t_sys_user_achievements on t_sys_region.ID=t_sys_user_achievements.BIG_AREA_ID where 1=1 and t_sys_region.del_flag!=1 AND t_sys_region.PARENT_ID=0\n" +
                "AND t_sys_user_achievements.BIG_MANAGER_TARGER_NUM is not NULL\n" +
                "AND t_sys_user_achievements.MANAGER_TARGET_NUM is not NULL\n");
        sb.append(condition);
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getRegionByUserId(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\t\tt_sys_region.ID AS id,\n" +
                "t_sys_region.`NAME` AS regionName\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "WHERE\n" +
                "\tUSER_ID = '" + userId + "'\n" +
                "AND t_sys_region.PARENT_ID = '0'");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getCount(String roleNames, String regionId, String companyId, String departName) {
        StringBuffer sb = new StringBuffer();
        sb.append("select count(userInfo.userId) as userNum from(" +
                "SELECT v_sys_part_time_for_report.userId as userId\n" +
                "FROM v_sys_part_time_for_report \n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=v_sys_part_time_for_report.departId\n" +
                "WHERE 1=1\n" +
                "AND v_sys_part_time_for_report.userId IN (\n" +
                "SELECT t_sys_user_2_region.USER_ID AS userId\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_user_2_region.REGION_ID='" + regionId + "'\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                ")\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_depart.`NAME` ='" + departName + "'\n" +
                "AND v_sys_part_time_for_report.jobState!=1\n" +
                "and t_sys_role_name.`NAME` in (" + roleNames + ")\n" +
                "GROUP BY v_sys_part_time_for_report.userId) userInfo");
        JSONObject json = super.queryForJsonObject(sb.toString());
        return json;
    }

    public List<JSONObject> getBigRegionByRoleName(String roleName, String regionId, String companyId, String departName) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT v_sys_part_time_for_report.userId as userId\n" +
                "FROM v_sys_part_time_for_report \n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=v_sys_part_time_for_report.departId\n" +
                "WHERE 1=1\n" +
                "AND v_sys_part_time_for_report.userId IN (\n" +
                "SELECT t_sys_user_2_region.USER_ID AS userId\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_user_2_region.REGION_ID='" + regionId + "'\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                ")\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_depart.`NAME` ='" + departName + "'\n" +
                "AND v_sys_part_time_for_report.jobState!=1\n" +
                "and t_sys_role_name.`NAME` ='" + roleName + "'\n" +
                "GROUP BY v_sys_part_time_for_report.userId\n");
        List<JSONObject> json = super.queryForJsonList(sb.toString());
        return json;
    }

    public List<JSONObject> getDirectorByCompanyId(String roleName, String companyId, String userId, String regionId) {
        StringBuffer sb = new StringBuffer();
        sb.append("\n" +
                "SELECT v_sys_part_time_for_report.*,t_sys_role_name.`NAME` FROM v_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID = v_sys_part_time_for_report.roleNameId\n" +
                "\n" +
                "WHERE companyId='" + companyId + "'\n" +
                " AND v_sys_part_time_for_report.userId in(SELECT t_sys_user_2_region.USER_ID FROM t_sys_user_2_region WHERE t_sys_user_2_region.REGION_ID='" + regionId + "')\n" +
                "AND t_sys_role_name.`NAME`='" + roleName + "'\n" +
                "AND v_sys_part_time_for_report.userId NOT in(" + userId + ")\n" +
                "AND v_sys_part_time_for_report.jobState !=1");
        List<JSONObject> json = super.queryForJsonList(sb.toString());
        return json;
    }

    public List<JSONObject> getBigRegionByManager(String target, Integer type, String dateTime, String yesterday, String companyId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , ");
        sb.append(" CASE WHEN @rowtotal = obj.averageComplete THEN \n");
        sb.append(" @rownum WHEN @rowtotal := obj.averageComplete THEN\n");
        sb.append(" @rownum :=@rownum + 1 ");
        sb.append(" WHEN @rowtotal = 0 THEN ");
        sb.append(" @rownum :=@rownum + 1 \n");
        sb.append(" END AS rownum \n");
        sb.append(" FROM( \n");
        sb.append(" SELECT \n");
        sb.append(" region.`COMPANY_ID` AS companyId, \n");
        sb.append(" REPLACE(region.`NAME`," + "'" + "大区" + "'," + "''" + ") AS regionName, \n");
        sb.append(" v_sys_region.managerName,\n");
        sb.append(" v_sys_company.managerName as topManagerName,\n ");
        sb.append(" (SELECT COUNT(1) \n ");
        sb.append(" from t_wrt_customer_visit LEFT JOIN v_sys_user ON t_wrt_customer_visit.CREATE_USER_ID=v_sys_user.id where \n ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "'\n");
            } else {
                sb.append(" t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "'\n");
            }
        } else {
            sb.append("  LEFT(t_wrt_customer_visit.REPORT_DATE,7)= " + "'" + dateTime + "'\n");
        }
        sb.append(" AND v_sys_user.roleName='区域经理' AND region.id = t_wrt_customer_visit.BIG_REGION_ID and t_wrt_customer_visit.VISIT_MODE =" + type + ") as complete, \n ");
        sb.append(" (select IFNULL(sum(t_sys_target." + target + "),0) from t_sys_target where \n ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" t_sys_target.TARGET_DATE=" + "'" + dateTime + "'\n");
            } else {
                sb.append(" t_sys_target.TARGET_DATE=" + "'" + dateTime + "'\n");
            }
        } else {
            sb.append("  LEFT(t_sys_target.TARGET_DATE,7)= " + "'" + dateTime + "'\n");
        }
        sb.append(" AND t_sys_target.BIG_REGION_ID=region.ID) as target,\n ");
        sb.append(" IFNULL((SELECT COUNT(1) FROM t_sys_leave  \n ");
        sb.append(" LEFT JOIN v_sys_user ON v_sys_user.id=t_sys_leave.USER_ID\n ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" WHERE t_sys_leave.LEAVE_DATE=" + "'" + dateTime + "'\n");
            } else {
                sb.append(" WHERE t_sys_leave.LEAVE_DATE=" + "'" + dateTime + "'\n");
            }
        } else {
            sb.append(" WHERE  LEFT(t_sys_leave.LEAVE_DATE,7)= " + "'" + dateTime + "'\n");
        }
        sb.append(" and v_sys_user.roleName='区域经理' AND v_sys_user.departId in\n ");
        sb.append("  (SELECT id from t_sys_depart where `name`=region.`NAME`) and v_sys_user.delFlag=0 GROUP BY v_sys_user.departId),0) as leaveNumber,\n ");
        sb.append(" IFNULL((select sum(v_sys_user.roleName='区域经理') FROM v_sys_user WHERE v_sys_user.departId in ( \n ");
        sb.append(" SELECT id from t_sys_depart where `name`=region.`NAME` \n ");
        sb.append(" ) and v_sys_user.delFlag=0  ),0) as regionManagerNum,\n ");
        sb.append("  IFNULL((select t_sys_role.TARGET_NUMBER  FROM t_sys_role \n ");
        sb.append("  LEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID=t_sys_role_name.ID \n ");
        sb.append("  LEFT JOIN t_sys_depart ON t_sys_role.DEPART_ID=t_sys_depart.ID \n ");
        sb.append("  WHERE t_sys_role_name.`name`='区域经理' AND t_sys_role.DEL_FLAG=0 AND t_sys_depart.`NAME`=region.name \n ");
        sb.append("  GROUP BY t_sys_role.ID  ),0) AS managerTargetNum, \n ");
        if (len == 10) {
            sb.append(" (select IFNULL(TRUNCATE(complete/(regionManagerNum-IFNULL(leaveNumber,0)),2),0.00)) as  averageComplete,\n ");
        } else {
            sb.append(" (select IFNULL(TRUNCATE(complete/regionManagerNum,2),0.00)) as  averageComplete,\n ");
        }
        sb.append(" (select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate\n ");
        sb.append(" from t_sys_region AS region \n");
        sb.append(" LEFT JOIN v_sys_region ON region.ID= v_sys_region.regionId and managerRoleName='大区经理' AND v_sys_region.managerDelFlag=0 ");
        sb.append(" LEFT JOIN v_sys_company on region.COMPANY_ID=v_sys_company.companyId \n");
        sb.append(" where region.COMPANY_ID=" + "'" + companyId + "'\n ");
        sb.append(" AND region.PARENT_ID=0 ");
        sb.append(" AND region.`NAME` NOT LIKE '%大客户%'\n");
        sb.append(" GROUP BY  region.ID   \n");
        sb.append(" ORDER BY averageComplete DESC\n");
        sb.append(" ) as obj,   (SELECT @rownum := 0 ,@rowtotal := NULL) r  \n");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }


    public List<JSONObject> getRegionByUserId(String target, Integer type, String dateTime, String yesterday, String departId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , ");
        sb.append(" CASE WHEN @rowtotal = obj.complete THEN \n");
        sb.append(" @rownum WHEN @rowtotal := obj.complete THEN\n");
        sb.append(" @rownum :=@rownum + 1 ");
        sb.append(" WHEN @rowtotal = 0 THEN ");
        sb.append(" @rownum :=@rownum + 1 \n");
        sb.append(" END AS rownum \n");
        sb.append(" FROM( \n");
        sb.append(" SELECT \n");
        sb.append(" v_sys_region.managerId, \n");
        sb.append(" v_sys_region.managerName, \n");
        sb.append(" v_sys_region.regionId, \n");
        sb.append(" v_sys_region.regionName, \n");
        sb.append(" IFNULL(b_sys_target.target, 0) AS target,\n ");
        sb.append(" IFNULL(t_sys_complete.complete, 0) AS complete,\n ");
        if (len == 10) {
            String signDay = dateTime.substring(0, 7);
            sb.append(" (SELECT count(*) FROM t_sys_sign_in where LEFT(t_sys_sign_in.SIGN_DATE,7)=" + "'" + signDay + "' AND t_sys_sign_in.USER_ID=v_sys_region.managerId) AS signManagerNum,\n ");
        } else {
            sb.append(" (SELECT count(*) FROM t_sys_sign_in where LEFT(t_sys_sign_in.SIGN_DATE,7)=" + "'" + dateTime + "' AND t_sys_sign_in.USER_ID=v_sys_region.managerId) AS signManagerNum,\n ");
        }
        sb.append(" (select IFNULL(TRUNCATE(complete/signManagerNum,2),0.00)) as  averageComplete, \n ");
        sb.append(" (select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate \n ");
        sb.append(" FROM \n ");
        sb.append(" v_sys_region\n ");
        sb.append(" LEFT JOIN ( SELECT REGION_ID, \n ");
        sb.append(" SUM(" + target + ") AS target FROM\n ");
        sb.append(" t_sys_target WHERE\n ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" TARGET_DATE =" + "'" + dateTime + "'\n");
            } else {
                sb.append(" TARGET_DATE =" + "'" + dateTime + "'\n");
            }
        } else {
            sb.append("  LEFT(TARGET_DATE,7)= " + "'" + dateTime + "'\n");
        }
        sb.append(" GROUP BY REGION_ID \n");
        sb.append(" ) AS b_sys_target ON v_sys_region.regionId = b_sys_target.REGION_ID ");
        sb.append(" LEFT JOIN ( SELECT CREATE_USER_ID, \n");
        sb.append(" SUM(VISIT_MODE = " + type + ") AS complete \n");
        sb.append(" FROM  \n");
        sb.append(" t_wrt_customer_visit WHERE\n");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" REPORT_DATE= " + "'" + dateTime + "'\n");
            } else {
                sb.append(" REPORT_DATE= " + "'" + dateTime + "'\n");
            }
        } else {
            sb.append(" LEFT(REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" GROUP BY CREATE_USER_ID ");
        sb.append(" ) AS t_sys_complete ON v_sys_region.managerId = t_sys_complete.CREATE_USER_ID \n ");
        sb.append(" WHERE 1=1  ");
        sb.append(" AND managerDepartId = " + "'" + departId + "'\n");
        sb.append(" AND managerRoleName = '区域经理' \n");
        sb.append(" AND regionDelFlag = 0 \n");
        sb.append(" AND managerDelFlag = 0 \n");
        sb.append(" AND parentId != 0 \n");
        sb.append(" GROUP BY v_sys_region.managerId\n ");
        sb.append(" ORDER BY complete DESC,v_sys_region.regionSortNo \n");
        sb.append(" ) as obj,  ");
        sb.append(" (SELECT @rownum := 0 ,@rowtotal := NULL) r ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getRegionByManage(String target, Integer type, String dateTime, String yesterday, String companyId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT obj.* , ");
        sb.append(" CASE WHEN @rowtotal = obj.complete THEN \n");
        sb.append(" @rownum WHEN @rowtotal := obj.complete THEN\n");
        sb.append(" @rownum :=@rownum + 1 ");
        sb.append(" WHEN @rowtotal = 0 THEN ");
        sb.append(" @rownum :=@rownum + 1 \n");
        sb.append(" END AS rownum \n");
        sb.append(" FROM( \n");
        sb.append(" SELECT \n");
        sb.append(" v_sys_region.managerId, \n");
        sb.append(" v_sys_region.managerName, \n");
        sb.append(" v_sys_region.regionId, \n");
        sb.append(" v_sys_region.bigmanagerName,\n ");
        sb.append(" REPLACE(v_sys_region.bigRegionName," + "'" + "大区" + "'," + "''" + ") as bigRegionName, \n");
        sb.append(" IFNULL(b_sys_target.target, 0) AS target,\n ");
        sb.append(" IFNULL(t_sys_complete.complete, 0) AS complete,\n ");
        if (len == 10) {
            String signDay = dateTime.substring(0, 7);
            sb.append(" (SELECT count(*) FROM t_sys_sign_in where LEFT(t_sys_sign_in.SIGN_DATE,7)=" + "'" + signDay + "' AND t_sys_sign_in.USER_ID=v_sys_region.managerId) AS signManagerNum,\n ");
        } else {
            sb.append(" (SELECT count(*) FROM t_sys_sign_in where LEFT(t_sys_sign_in.SIGN_DATE,7)=" + "'" + dateTime + "' AND t_sys_sign_in.USER_ID=v_sys_region.managerId) AS signManagerNum,\n ");
        }
        sb.append(" (select IFNULL(TRUNCATE(complete/signManagerNum,2),0.00)) as  averageComplete, \n ");
        sb.append(" (select IFNULL(TRUNCATE(complete/target*100,2),0.00)) as  completionRate \n ");
        sb.append(" FROM \n ");
        sb.append(" v_sys_region\n ");
        sb.append(" LEFT JOIN ( SELECT USER_ID, \n ");
        sb.append(" SUM(" + target + ") AS target FROM\n ");
        sb.append(" t_sys_target WHERE\n ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" TARGET_DATE =" + "'" + dateTime + "'\n");
            } else {
                sb.append(" TARGET_DATE =" + "'" + dateTime + "'\n");
            }
        } else {
            sb.append("  LEFT(TARGET_DATE,7)= " + "'" + dateTime + "'\n");
        }
        sb.append(" GROUP BY USER_ID\n");
        sb.append(" ) AS b_sys_target ON v_sys_region.managerId = b_sys_target.USER_ID ");
        sb.append(" LEFT JOIN ( SELECT CREATE_USER_ID, \n");
        sb.append(" SUM(VISIT_MODE = " + type + ") AS complete \n");
        sb.append(" FROM  \n");
        sb.append(" t_wrt_customer_visit WHERE\n");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append(" REPORT_DATE= " + "'" + dateTime + "'\n");
            } else {
                sb.append(" REPORT_DATE= " + "'" + dateTime + "'\n");
            }
        } else {
            sb.append(" LEFT(REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" GROUP BY CREATE_USER_ID ");
        sb.append(" ) AS t_sys_complete ON v_sys_region.managerId = t_sys_complete.CREATE_USER_ID \n ");
        sb.append(" WHERE 1=1  ");
        sb.append(" AND companyId = " + "'" + companyId + "'\n");
        sb.append(" AND managerRoleName = '区域经理' \n");
        sb.append(" AND regionDelFlag = 0 \n");
        sb.append(" AND managerJobState != 1 \n");
        sb.append(" AND parentId != 0 \n");
        sb.append(" GROUP BY v_sys_region.managerId\n ");
        sb.append(" ORDER BY complete DESC,v_sys_region.regionSortNo \n");
        sb.append(" ) as obj,  ");
        sb.append(" (SELECT @rownum := 0 ,@rowtotal := NULL) r ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getRegionByManageChart(String dateTime, String yesterday, String companyId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n");
        sb.append(" v_sys_region.managerName, \n");
        sb.append(" IFNULL(t_sys_tel.complete, 0) AS telComplete, \n ");
        sb.append(" IFNULL(t_sys_face.complete, 0) AS faceComplete \n ");
        sb.append(" FROM \n ");
        sb.append(" v_sys_region\n ");
        sb.append(" LEFT JOIN ( SELECT REGION_ID, \n");
        sb.append(" SUM(VISIT_MODE = 0) AS complete \n");
        sb.append(" FROM  \n");
        sb.append(" t_wrt_customer_visit WHERE\n");
        if (len == 10) {
            sb.append(" REPORT_DATE= " + "'" + dateTime + "'\n");
        } else {
            sb.append(" LEFT(REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" GROUP BY REGION_ID ");
        sb.append(" ) AS t_sys_tel ON v_sys_region.regionId = t_sys_tel.REGION_ID  \n ");
        sb.append(" LEFT JOIN ( SELECT REGION_ID, \n");
        sb.append(" SUM(VISIT_MODE = 1) AS complete \n");
        sb.append(" FROM  \n");
        sb.append(" t_wrt_customer_visit WHERE\n");
        if (len == 10) {
            sb.append(" REPORT_DATE= " + "'" + dateTime + "'\n");
        } else {
            sb.append(" LEFT(REPORT_DATE,7)= " + "'" + dateTime + "' \n");
        }
        sb.append(" GROUP BY REGION_ID ");
        sb.append(" ) AS t_sys_face ON v_sys_region.regionId = t_sys_face.REGION_ID  \n ");
        sb.append(" WHERE 1=1  ");
        sb.append(" AND companyId = " + "'" + companyId + "'\n");
        sb.append(" AND managerRoleName = '区域经理' \n");
        sb.append(" AND regionDelFlag = 0 \n");
        sb.append(" AND managerDelFlag = 0 \n");
        sb.append(" AND parentId != 0 \n");
        sb.append(" GROUP BY v_sys_region.managerId \n ");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getRegionByChart(String dateTime, String yesterday, String companyId) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n ");
        sb.append(" REPLACE(t_sys_region.`NAME`," + "'" + "大区" + "'," + "''" + ") as regionName,\n ");
        sb.append(" (select sum(v_sys_user.roleName='区域经理') FROM v_sys_user WHERE v_sys_user.departId in ( \n ");
        sb.append(" SELECT id from t_sys_depart where `name`=t_sys_region.`NAME` \n ");
        sb.append(" ) and v_sys_user.delFlag=0  ) as regionManagerNum, \n ");
        if (len == 10) {
            sb.append("(select IFNULL(TRUNCATE(sum(t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "'\n");
        } else {
            sb.append("(select IFNULL(TRUNCATE(sum((LEFT(t_wrt_customer_visit.REPORT_DATE,7) =" + "'" + dateTime + "'" + ")" + "\n ");
        }
        sb.append(" and t_wrt_customer_visit.VISIT_MODE =0)/regionManagerNum,2),'0.00'))\n");
        sb.append(" as averageTelComplete, \n");
        if (len == 10) {
            sb.append("(select IFNULL(TRUNCATE(sum(t_wrt_customer_visit.REPORT_DATE=" + "'" + dateTime + "'" + " \n");
        } else {
            sb.append("(select IFNULL(TRUNCATE(sum((LEFT(t_wrt_customer_visit.REPORT_DATE,7) =" + "'" + dateTime + "'" + ")" + " \n");
        }
        sb.append(" and t_wrt_customer_visit.VISIT_MODE =1)/regionManagerNum,2),'0.00'))  as averageFaceComplete\n");
        sb.append(" FROM t_sys_region \n");
        sb.append(" LEFT JOIN t_wrt_customer_visit ON t_sys_region.id = t_wrt_customer_visit.BIG_REGION_ID \n ");
        sb.append(" WHERE 1=1 \n ");
        sb.append(" AND t_sys_region.COMPANY_ID=" + "'" + companyId + "'\n");
        sb.append(" AND t_sys_region.PARENT_ID = 0 \n ");
        sb.append(" AND t_sys_region.`NAME` NOT LIKE '%大客户%'\n");
        sb.append(" GROUP BY  t_sys_region.ID");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getPeopleByUserId(String userId, String dateTime, String yesterday, Integer type) {
        int len = dateTime.length();
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        sb.append("b.TEL AS tel,\n");
        sb.append("b.NAME AS name, \n");
        sb.append("b.WECHAT AS wechat, \n");
        sb.append("a.ID AS id,");
        sb.append("a.REPORT_DATE AS date,");
        sb.append("a.ARRIVALS_TIME AS arrivalsTime,\n");
        sb.append("a.LEAVE_TIME AS leaveTime, \n");
        sb.append("IFNULL(CEIL(CEILING(UNIX_TIMESTAMP(a.LEAVE_TIME)-UNIX_TIMESTAMP(a.ARRIVALS_TIME))/60),0) as time \n");
        sb.append("FROM \n");
        sb.append("`t_wrt_customer_visit` as a  \n");
        sb.append("LEFT JOIN t_wrt_customer as b ON a.CUSTOMER_ID = b.ID  \n");
        sb.append("WHERE a.CREATE_USER_ID='" + userId + "' AND \n");
        sb.append("a.VISIT_MODE = " + type + " \n");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append("AND a.REPORT_DATE='" + dateTime + "' \n");
            } else {
                sb.append("AND a.REPORT_DATE='" + dateTime + "' \n");
            }
        } else {
            sb.append("AND LEFT(a.REPORT_DATE,7)='" + dateTime + "' \n");
        }
        sb.append(" ORDER BY a.REPORT_DATE DESC");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public JSONObject getPeopleInfoByUserId(String userId, String targetDate, String yesterday, Integer type) {
        int len = targetDate.length();
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");
        if (type.equals(token)) {
            sb.append("(SELECT SUM(FACE_TARGET) FROM t_sys_target ");
        } else {
            sb.append("(SELECT SUM(TEL_TARGET) FROM t_sys_target ");
        }
        sb.append("WHERE USER_ID='" + userId + "' AND ");
        if (len == 10) {
            sb.append("TARGET_DATE='" + targetDate + "'");
        } else {
            sb.append("LEFT(TARGET_DATE,7)='" + targetDate + "'");
        }
        sb.append(" ) as dayTarget,");
        sb.append("(SELECT COUNT(1) FROM ");
        sb.append("t_wrt_customer_visit WHERE ");
        sb.append("CREATE_USER_ID='" + userId + "' AND ");
        sb.append("VISIT_MODE = " + type + " AND ");
        if (len == 10) {
            if (token.equals(type)) {
                sb.append("REPORT_DATE='" + targetDate + "'");
            } else {
                sb.append("REPORT_DATE='" + targetDate + "'");
            }
        } else {
            sb.append("LEFT(REPORT_DATE,7)='" + targetDate + "'");
        }
        sb.append(") as complete ");
        JSONObject jsonObject = super.queryForJsonObject(sb.toString());
        return jsonObject;
    }

    /**
     * 获取区域id、大区id
     *
     * @param userId
     * @param condition
     * @return
     */
    public JSONObject getRegionByRoleName(String userId, String condition) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ID as id,NAME AS name FROM T_SYS_REGION WHERE ID IN( ");
        sb.append("SELECT REGION_ID FROM T_SYS_USER_2_REGION WHERE USER_ID='" + userId + "' ) " + condition);
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList.size() > 0 ? jsonList.get(0) : null;
    }

    public JSONObject getBigRegionByRoleName(String userId, String condition) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT\n" +
                "\tt_sys_depart.ID as id,t_sys_depart.`NAME` AS name,t_sys_user.`NAME` AS userName\n" +
                "FROM\n" +
                "\tt_sys_depart\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.DEPART_ID = t_sys_depart.ID\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "WHERE 1=1 AND t_sys_user.ID = '" + userId + "' ");
        sb.append(condition);
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList.size() > 0 ? jsonList.get(0) : null;
    }

    //根据RoleNames和区域获取上级
    public List<JSONObject> getParents(String roleNames, String userId, String regionId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t_sys_user.id as userId from t_sys_user \n" +
                "WHERE ID in(\n" +
                "SELECT USER_ID from t_sys_user_2_region WHERE REGION_ID = '" + regionId + "'\n" +
                ") \n" +
                "and (\n" +
                "\tROLE_ID in (\n" +
                "\t\tSELECT ID from t_sys_role WHERE ROLE_NAME_ID in (SELECT ID from t_sys_role_name WHERE `NAME` in (" + roleNames + "))\n" +
                "\t)\n" +
                "\tOR ID in(SELECT user_id from t_sys_user_2_role WHERE ROLE_ID in (SELECT id from t_sys_role where ROLE_NAME_ID in (SELECT ID from t_sys_role_name WHERE `NAME` in (" + roleNames + "))))\n" +
                ")\n" +
                "AND t_sys_user.ID!='" + userId + "'\n");
        return super.queryForJsonList(sb.toString());
    }

    //获取该大区缺少区域经理的区域
    public List<JSONObject> getLackRegion(String roleNames, String regionId, String companyId, String departName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "t_sys_region.ID AS regionId,\n" +
                "t_sys_region.`NAME` AS regionName\n" +
                "FROM\n" +
                "t_sys_region\n" +
                "WHERE 1=1\n" +
                "AND t_sys_region.PARENT_ID='" + regionId + "'\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND t_sys_region.ID NOT in(\n" +
                "SELECT\n" +
                "t_sys_region.ID AS regionId\n" +
                "FROM\n" +
                "v_sys_part_time_for_report\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_part_time_for_report.userId\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID=t_sys_user_2_region.REGION_ID\n" +
                "LEFT JOIN t_sys_role_name ON v_sys_part_time_for_report.roleNameId=t_sys_role_name.ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=v_sys_part_time_for_report.departId\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role_name.`NAME` in(" + roleNames + ")\n" +
                "AND v_sys_part_time_for_report.companyId='" + companyId + "'\n" +
                "AND t_sys_depart.`NAME`='" + departName + "'\n" +
                "AND v_sys_part_time_for_report.jobState<>1\n" +
                "AND t_sys_region.PARENT_ID<>0\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                ")");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getUserByRegionId(String roleNames, String regionId, String userIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT\n" +
                "\tv_sys_user.id AS userId\n" +
                "FROM\n" +
                "\tv_sys_user\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID = v_sys_user.id\n" +
                "LEFT JOIN t_sys_region ON t_sys_region.ID = t_sys_user_2_region.REGION_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_region.DEL_FLAG = 0\n" +
                "AND v_sys_user.jobState!=1\n" +
                "AND t_sys_region.PARENT_ID = 0\n" +
                "AND t_sys_region.id = '" + regionId + "'\n" +
                "AND v_sys_user.roleName IN (" + roleNames + ")\n" +
                "AND t_sys_user_2_region.USER_ID IN (" + userIds + ")\n" +
                "GROUP BY\n" +
                "\tv_sys_user.id");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getPartJobRegionForDaQu(String userId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT\n" +
                "\tt_sys_region.ID AS id,\n" +
                "\tt_sys_region.`NAME` AS regionName\n" +
                "FROM\n" +
                "\tt_sys_user_2_region\n" +
                "LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID = t_sys_region.ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_region.COMPANY_ID\n" +
                "LEFT JOIN v_sys_user ON t_sys_user_2_region.USER_ID = v_sys_user.id\n" +
                "AND v_sys_user.departName = t_sys_region.`NAME`\n" +
                "AND v_sys_user.companyId = t_sys_region.COMPANY_ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND t_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "AND t_sys_region.PARENT_ID = '0'\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_region.DEL_FLAG=0\n" +
                "AND v_sys_user.departName is not null");
        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

}
